package cdms.db.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import cdms.db.ManagerRepository;
import cdms.domain.Manager;
import cdms.web.PaginationSupport;

/**
 * manager Interface implementation class
 * 
 * @author haozhiwei
 * @version v1.0
 */
@Repository
public class JdbcManagerRepository implements ManagerRepository {

	private JdbcTemplate jdbc;

	@Autowired
	public JdbcManagerRepository(JdbcTemplate jdbc) {
		this.jdbc = jdbc;
	}

	@Override
	public long count() {
		return jdbc.queryForLong("select count(id) from Manager where isDeleted=false");
	}

	@Override
	public long countByUserName(String search) {
		search="%"+search+"%";
		return jdbc.queryForLong("select count(id) from Manager where isDeleted=false and username like ?", search);
	}
	
	@Override
	public long countByFullName(String search) {
		search="%"+search+"%";
		return jdbc.queryForLong("select count(id) from Manager where isDeleted=false and fullname like ?", search);
	}
	
	@Override
	public Manager save(Manager manager) {
		jdbc.update(INSERT_MANAGER, manager.getUserName(), manager.getPassword(), manager.getFullName(),
				manager.getEmail(), manager.getPhoneNo(), manager.isDelete());
		return manager;
	}

	@Override
	public Manager update(Manager manager) {
		if (manager.getPassword() == null) {
			jdbc.update(UPDATE_MANAGER, manager.getFullName(), manager.getEmail(), manager.getPhoneNo(),
					manager.getUserName());
		} else {
			jdbc.update(UPDATE_MANAGERPASSWORD, manager.getPassword(), manager.getUserName());
		}
		manager = findByUserName(manager.getUserName());
		return manager;
	}

	@Override
	public boolean delete(Long id) {
		jdbc.update(DELETE_MANAGER, id);
		return true;
	}

	@Override
	public Manager findOne(Long id) {
		Manager manager = null;
		try {
			manager = jdbc.queryForObject(SELECT_MANAGER + " and id=?", new ManagerRowMapper(), id);
		} catch (DataAccessException e) {
		}
		return manager;
	}

	@Override
	public Manager findByUserName(String userName) {
		Manager manager = null;
		try {
			manager = jdbc.queryForObject(SELECT_ALL + " where username=?", new ManagerRowMapper(), userName);
		} catch (DataAccessException e) {
		}
		return manager;
	}

	@Override
	public Manager findByUserName(String userName, String password) {
		Manager manager = null;
		try {
			manager = jdbc.queryForObject(SELECT_MANAGER + " and username=? and password=?", new ManagerRowMapper(),
					userName, password);
		} catch (DataAccessException e) {
		}
		return manager;
	}

	@Override
	public List<Manager> findAll() {
		return jdbc.query(SELECT_MANAGER + " order by id", new ManagerRowMapper());
	}

	private static class ManagerRowMapper implements RowMapper<Manager> {
		public Manager mapRow(ResultSet rs, int rowNum) throws SQLException {
			return new Manager(rs.getLong("id"), rs.getString("username"), null, rs.getString("fullname"),
					rs.getString("email"), rs.getString("phoneNo"), rs.getBoolean("isdeleted"));
		}
	}

	@Override
	public PaginationSupport<Manager> findPage(int pageNo, int pageSize) {
		int totalCount = (int) count();
		int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
		if (totalCount < 1)
			return new PaginationSupport<Manager>(new ArrayList<Manager>(0), 0);

		List<Manager> items = jdbc.query(SELECT_PAGE_MANAGERS, new ManagerRowMapper(), pageSize, startIndex);
		PaginationSupport<Manager> ps = new PaginationSupport<Manager>(items, totalCount, pageSize, startIndex);
		return ps;
	}
	
	@Override
	public PaginationSupport<Manager> searchByUserName(int pageNo, int pageSize,String search) {
		int totalCount = (int) countByUserName(search);
		search="%"+search+"%";
		int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
		if (totalCount < 1)
			return new PaginationSupport<Manager>(new ArrayList<Manager>(0), 0);

		List<Manager> items = jdbc.query(SEARCH_BY_USERNAME_PAGE, new ManagerRowMapper(), search, pageSize, startIndex);
		PaginationSupport<Manager> ps = new PaginationSupport<Manager>(items, totalCount, pageSize, startIndex);
		return ps;
	}
	
	@Override
	public PaginationSupport<Manager> searchByFullName(int pageNo, int pageSize,String search) {
		int totalCount = (int) countByFullName(search);
		search="%"+search+"%";
		int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
		if (totalCount < 1)
			return new PaginationSupport<Manager>(new ArrayList<Manager>(0), 0);

		List<Manager> items = jdbc.query(SEARCH_BY_FULLNAME_PAGE, new ManagerRowMapper(), search, pageSize, startIndex);
		PaginationSupport<Manager> ps = new PaginationSupport<Manager>(items, totalCount, pageSize, startIndex);
		return ps;
	}

	private static final String INSERT_MANAGER = "insert into Manager (username,password,fullname,email,phoneNo,isDeleted) values (?, ?, ?, ?, ?,?)";

	private static final String SELECT_ALL = "select id, username, fullname,email,phoneNo,isDeleted from Manager ";

	private static final String SELECT_MANAGER = SELECT_ALL+" where isDeleted=false";
	
	private static final String DELETE_MANAGER = "update Manager set isDeleted=true  where id = ?";

	private static final String UPDATE_MANAGER = "update Manager set fullName=?,email=?,phoneNo=?  where userName = ?";

	private static final String UPDATE_MANAGERPASSWORD = "update Manager set password=?  where userName = ?";

	private static final String SELECT_PAGE_MANAGERS = SELECT_MANAGER + " order by id desc limit ? offset  ?";

	private static final String SEARCH_BY_USERNAME = SELECT_MANAGER + " and username like ?" ;
	
	private static final String SEARCH_BY_USERNAME_PAGE = SEARCH_BY_USERNAME + " order by id desc limit ? offset  ?" ;
	
	private static final String SEARCH_BY_FULLNAME = SELECT_MANAGER + " and fullname like ? ";
	
	private static final String SEARCH_BY_FULLNAME_PAGE = SEARCH_BY_FULLNAME + " order by id desc limit ? offset  ?" ;

}
